/**
 * 在这里定义和用户相关的路由处理函数，供 /router/user.js 模块进行调用
 */
//导入数据库操作模块
var models = require('../db/index');
var mysql = require('mysql');

// 连接数据库
var conn = mysql.createConnection(models.mysql);
conn.connect();

//新增学员信息
exports.addStudent = (req, res) => {
    var info = req.body;
    console.log(15, info);
    const sql = `insert into student set ?`;
    conn.query(sql, info, function (err, results) {
        // 执行 SQL 语句失败
        if (err) return res.cc(err);
        // SQL 语句执行成功，但影响行数不为 1
        if (results.affectedRows !== 1) {
            return res.cc('新增失败，请稍后再试！');
        } else {
            return res.send({
                status: 200,
                message: '新增学员成功',
            })
        }
    })
}

// 查询学员
exports.getStudent = (req, res) => {
    const sql = `select * from student`;
    conn.query(sql, function (err, results) {
        // 执行 SQL 语句失败
        if (err) return res.cc(err);
        // SQL 语句执行成功，但影响行数不为 1
        // if (results.affectedRows !== 1) return res.cc('查询失败，请稍后再试！');
        res.send({
            status: 200,
            message: '查询学员成功',
            data: results
        })
    })
}

//修改学员信息
exports.editStudent = (req, res) => {
    var info = req.body;
    var body = req.params;
    console.log(333,info);
    const sql = `update student set sname='${info.sname}',school='${info.school}',consulteacher='${info.consulteacher}',
    gradtime='${info.gradtime}',studentstate='${info.studentstate}',phone='${info.phone}' where sid='${body.id}'`;
    conn.query(sql, (err, results) => {
        if (err) return res.cc(err);
        res.send({
            status: 200,
            message: '修改成功'
        })
    })
}

// 删除学员
exports.deleteStduent = (req, res) => {
    var query = req.query;
    const sql = `delete from student where sid = ?;`;
    conn.query(sql, query.id, (err, results) => {
        if (err) return res.cc(err);
        res.send({
            status: 200,
            message: '删除成功'
        })
    })
}

// 模糊查询学员名字
exports.obscureStudent = (req, res) => {
    var body = req.body;
    const sql = `select * from student where sname like '%${body.sname}%';`;
    conn.query(sql, (err, results) => {
        if (err) return res.cc(err);
        console.log(body,results);
        res.send({
            status: 200,
            message: '查询成功',
            data: results
        })
    })
}

// 模糊查询--咨询
exports.searchStudent = (req, res) => {
    var body = req.body;
    const sql = `select * from student where consulteacher like '%${body.consulteacher}%';`;
    conn.query(sql, (err, results) => {
        if (err) return res.cc(err);
        console.log(body,results);
        res.send({
            status: 200,
            message: '查询成功',
            data: results
        })
    })
}

// 模糊查询--学校
exports.searchSchool = (req, res) => {
    var body = req.body;
    const sql = `select * from student where school like '%${body.school}%';`;
    conn.query(sql, (err, results) => {
        if (err) return res.cc(err);
        console.log(body,results);
        res.send({
            status: 200,
            message: '查询成功',
            data: results
        })
    })
}

// 模糊查询--班级
exports.searchClass = (req, res) => {
    var body = req.body;
    const sql = `select * from student where cid like '%${body.cid}%';`;
    conn.query(sql, (err, results) => {
        if (err) return res.cc(err);
        console.log(body,results);
        res.send({
            status: 200,
            message: '查询成功',
            data: results
        })
    })
}

// 模糊查询--状态
exports.searchState = (req, res) => {
    var body = req.body;
    const sql = `select * from student where studentstate like '${body.studentstate}';`;
    conn.query(sql, (err, results) => {
        if (err) return res.cc(err);
        console.log(body,results);
        res.send({
            status: 200,
            message: '查询成功',
            data: results
        })
    })
}

// 模糊查询--时间
exports.searchTime = (req, res) => {
    var body = req.body;
    const sql = `select * from student where gradtime like '${body.gradtime}';`;
    conn.query(sql, (err, results) => {
        if (err) return res.cc(err);
        console.log(body,results);
        res.send({
            status: 200,
            message: '查询成功',
            data: results
        })
    })
}

// let body=req.body;
// const sql = `select * from student where studentstate like '%${body.studentstate}%' ${body.week !==''? `and week like %${body.week}%`:''};`;